﻿create PROCEDURE Sync.SP_Users_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Users_Temp TABLE 
		(
			UserID UniqueIdentifier,
			[Username] nvarchar(50),
				[Password] nvarchar(50),
				[DisplayName] nvarchar(50),
				[Birthdate] datetime,
				[JobTitle] nvarchar(50),
				[Email] nvarchar(50),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Users_Temp 
	   SELECT	Tbl.Col.value('UserID[1]','uniqueidentifier') UserID,
                Tbl.Col.value('Username[1]', 'nvarchar(50)') [Username],
Tbl.Col.value('Password[1]', 'nvarchar(50)') [Password],
Tbl.Col.value('DisplayName[1]', 'nvarchar(50)') [DisplayName],
Tbl.Col.value('Birthdate[1]', 'datetime') [Birthdate],
Tbl.Col.value('JobTitle[1]', 'nvarchar(50)') [JobTitle],
Tbl.Col.value('Email[1]', 'nvarchar(50)') [Email],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Users') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Users] ([UserID],[Username],[Password],[DisplayName],[Birthdate],[JobTitle],[Email])
	SELECT t.[UserID],t.[Username],t.[Password],t.[DisplayName],t.[Birthdate],t.[JobTitle],t.[Email]
	FROM  @Users_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Users_Tracking s
        WHERE t.UserID = s.UserID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Users]
	SET [Users].[Username] = t.[Username],[Users].[Password] = t.[Password],[Users].[DisplayName] = t.[DisplayName],[Users].[Birthdate] = t.[Birthdate],[Users].[JobTitle] = t.[JobTitle],[Users].[Email] = t.[Email] 
	FROM [dbo].[Users] s JOIN @Users_Temp t 
    ON t.UserID = s.UserID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Users_Tracking r
				  WHERE t.UserID = r.UserID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Users]
    FROM @Users_Temp t JOIN [dbo].[Users] s
    ON t.UserID = s.UserID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














